<?php
require_once 'conf.php';
require_once 'util.php';
require_once 'classes.php';
require_once 'viewbean/Transaction.php';
require_once 'viewbean/Account.php';
require_once 'viewbean/BudgetItem.php';
require_once 'viewbean/AutoReconcile.php';
require_once 'viewbean/SubAccount.php';
require_once 'viewbean/KeyValueBean.php';
require_once "services/securityServices.php";

class BudgetService {

	public function saveBudgetItemAccountAssociation($budgetitemid, $accountid){
		$userid = $_SESSION["userid"];
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("insert into budgetitem_account (budgetitemid, accountid, userid) values (:budgetitemid, :accountid, :userid)");
			$stmt->bindParam(':budgetitemid', $budgetitemid);
			$stmt->bindParam(':accountid', $accountid);
			$stmt->bindParam(':userid', $userid);
	
			$stmt->execute();
	
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('027');
		}
	}
	
	public function getBudgetItemsForUser($userid){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from budgetitem where userid = :userid order by description asc");
			$stmt->bindParam(':userid', $userid);
		
			$itemslist = array();
		
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$budgetitem = new BudgetItem($row["id"], $row["categoryId"], $row["description"], $row["expense"]);
					array_push($itemslist, $budgetitem);
				}
			}
		
		
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('016');
		}
		
		return $itemslist;
	}
	
	public function deleteBudgetItemAccountAssociation($id){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("delete from budgetitem_account where id = :id");
			$stmt->bindParam(':id', $id);
			
			$itemslist = array();
			$stmt->execute();
	
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('016');
		}
	}
	
	public function getBudgetItemAccountAssociation($userid){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from budgetitem_account where userid=:userid");
			$stmt->bindParam(':userid', $userid);
	
			$itemslist = array();
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$assocAccount = self::getAccountById($row["accountid"]);
					$budgetitem = self::getBudgetItemById($row["budgetitemid"]);
					$association = new KeyValueBean($row["id"], $budgetitem->description, $assocAccount->name);
					array_push($itemslist, $association);
				}
			}
	
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('016');
		}
		return $itemslist;
	}
	
	public function retrieveEquityDataForUser($userid){
		$service = new CommonServices;

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$stmt = $con->prepare("select * from equity_history where userid=:userid order by date asc");
			$stmt->bindParam(':userid', $userid);

			$itemslist = array();
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$transaction = new Transaction(null, null, $row["date"], $row["amount"], null, null, null, null, null);
					array_push($itemslist, $transaction);
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('016');
		}
		return $itemslist;
	}

	/**
	 * Gets the actual amount of a budget item in a specified budget
	 *
	 */
	public function getActualAmountForBudgetItemInBudget($budgetid, $budgetitemid){
		$budgetamount = 0;
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select budgetamount from budget_budgetitem where budgetid=:budgetid and budgetitemid=:budgetitemid");
			$stmt->bindParam(':budgetitemid', $budgetitemid);
			$stmt->bindParam(':budgetid', $budgetid);
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$budgetamount = $row["budgetamount"];
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('007');
		}
		return $budgetamount;
	}

	public function getBudgetByMonth($userid, $month){
		$budgetid = -1;
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from budget where userid=:userid and month=:month");
			$stmt->bindParam(':month', $month);
			$stmt->bindParam(':userid', $userid);
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$budgetid = $row["id"];
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('007');
		}
		return $budgetid;
	}

	public function getPastDataForBudgetItem($budgetItem, $month){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("update budgetitem set description=:description where id=:budgetItemId");
			$stmt->bindParam(':description', $description);
			$stmt->bindParam(':budgetItemId', $budgetItemId);
			$stmt->execute();

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('007');
		}
	}

	public function updateBudgetItem($budgetItemId, $description){
		if (strlen($description) < 1){
			throw new Exception('025');
		}
		error_log("Updating budget item: "+$budgetItemId);
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("update budgetitem set description=:description where id=:budgetItemId");
			$stmt->bindParam(':description', $description);
			$stmt->bindParam(':budgetItemId', $budgetItemId);
			$stmt->execute();

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('007');
		}
	}

	public function getBudgetItemsForCategory($budgetId, $categoryId){
		$service = new CommonServices;

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select b.budgetAmount, b.actualAmount, i.id, i.description, i.expense from budget_budgetitem b, budgetitem i where b.budgetid=:budgetId and i.id=b.budgetitemid and categoryId=:categoryId");
			$stmt->bindParam(':budgetId', $budgetId);
			$stmt->bindParam(':categoryId', $categoryId);

			$itemslist = array();
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$budgetItem = new BudgetItem($row["id"], $categoryId, $row["description"], $row["expense"]);
					$budgetItem->categoryDescription = self::getCategoryDescription($categoryId);
					$budgetItem->budgetAmount = $row["budgetAmount"];

					//we calculate the actual amount in the budget period
					$actualAmount = 0;

					$budgetStartDate = $service->getStartDateForBudget($budgetId);
					$budgetEndDate = $service->getEndDateForBudget($budgetId);

					$transactions = self::getBudgetItemTransactions($row["id"], $budgetStartDate, $budgetEndDate);

					foreach($transactions as $transaction){
						$actualAmount += $transaction->amount;
					}

					$budgetItem->actualAmount = $actualAmount;
					array_push($itemslist, $budgetItem);
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('016');
		}
		return $itemslist;
	}

	public function getCategoryDescription($categoryId){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select description from category where id=:categoryId");
			$stmt->bindParam(':categoryId', $categoryId);

			$description = 0;
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$description = $row["description"];
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('018');
		}

		return $description;
	}

	public function getBalanceForSubAccount($budgetItemId, $savingsAccId){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select sum(amount) as sum from transaction where budgetitemid=:budgetItemId and categoryId=4 and accountId=:savingsAccId");
			$stmt->bindParam(':budgetItemId', $budgetItemId);
			$stmt->bindParam(':savingsAccId', $savingsAccId);

			$sum = 0;
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$sum = $row["sum"];
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('018');
		}

		return $sum;
	}

	public function getAccountById($accountId){
		$service = new CommonServices;
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from account where id = :accountId");
			$stmt->bindParam(':accountId', $accountId);
	
			$account = null;
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$id = $row["id"];
					$name = $row["name"];
	
					$balance = $service->getBalanceForAcccount($id);
	
					$account = new Account($id, $name, $balance);
				}
			}
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('017');
		}
		return $account;
	}
	
	public function getAccountsForUser($userid){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from account where userid = :userid");
			$stmt->bindParam(':userid', $userid);

			$itemslist = array();
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$id = $row["id"];
					$name = $row["name"];

					$service = new CommonServices;

					$balance = $service->getBalanceForAcccount($id);

					$account = new Account($id, $name, $balance);
					array_push($itemslist, $account);
				}
			}
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('017');
		}
		return $itemslist;
	}

	public function getSumOfBudgetItemTransactions($budgetItemId, $fromDate, $toDate){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select sum(amount) as sum from transaction where budgetitemid=:budgetItemId and date(date) >= :fromDate and date(date) < :toDate");
			$stmt->bindParam(':fromDate', $fromDate);
			$stmt->bindParam(':toDate', $toDate);
			$stmt->bindParam(':budgetItemId', $budgetItemId);

			error_log("FROM DATE: ".$fromDate." AND TODATE: ".$toDate. " AND BUDGETITEMID: ".$budgetItemId);

			$transAmount = 0;
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$transAmount = $row["sum"];
				}
			}
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('017');
		}
		return $transAmount;
	}

	public function getBudgetItemTransactions($budgetItemId, $fromDate, $toDate){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from transaction where budgetitemid=:budgetItemId and date(date) >= :fromDate and date(date) <= :toDate");
			$stmt->bindParam(':fromDate', $fromDate);
			$stmt->bindParam(':toDate', $toDate);
			$stmt->bindParam(':budgetItemId', $budgetItemId);

			error_log("FROM DATE: ".$fromDate." AND TODATE: ".$toDate. " AND BUDGETITEMID: ".$budgetItemId);

			$itemslist = array();
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$transId = $row["id"];
					$description = $row["description"];
					$date = $row["date"];
					$transAmount = $row["amount"];
					$bankRef = $row["bankRef"];
					$budgetitemid = "";
					$reconciled = "";
					$categoryId = $row["categoryId"];
					$transType = "";

					$transaction = new Transaction($transId, $description, $date, $transAmount, $bankRef, $transType, $reconciled, $budgetitemid, $categoryId);
					array_push($itemslist, $transaction);
				}
			}
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('017');
		}
		return $itemslist;
	}

	public function createSubAccount($parentAccId, $budgetitemid, $openingBalance){
		try {
			$id = $this->getNextAutoIncrementValueForTable("subaccount");

			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("insert into subaccount (id, parentAccId, budgetitemid, openingBalance) values (:id, :parentAccId, :budgetitemid, :openingBalance)");
			$stmt->bindParam(':id', $id);
			$stmt->bindParam(':parentAccId', $parentAccId);
			$stmt->bindParam(':budgetitemid', $budgetitemid);
			$stmt->bindParam(':openingBalance', $openingBalance);

			$stmt->execute();

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('017');
		}
		return $id;
	}


	public function createBudgetItem($userid, $categoryId, $description){
		try {
			$id = $this->getNextAutoIncrementValueForTable("budgetitem");

			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("insert into budgetitem (id, categoryId, expense, description, userid) values (:id, :categoryId, 1, :description, :userid)");
			$stmt->bindParam(':id', $id);
			$stmt->bindParam(':categoryId', $categoryId);
			$stmt->bindParam(':description', $description);
			$stmt->bindParam(':userid', $userid);

			$stmt->execute();

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('017');
		}
		return $id;
	}


	public function getNextAutoIncrementValueForTable($tableName){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$dbName = GlobalConfig::db_name;
			$stmt = $con->prepare("SELECT Auto_increment as id FROM information_schema.tables WHERE table_name=:tableName and table_schema=:db_name");
			$stmt->bindParam(':tableName', $tableName);
			$stmt->bindParam(':db_name', $dbName);

			$result = 0;
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$result = $row["id"];
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('018');
		}

		return $result;
	}

	public function getNewBudgetId($userid){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from budget where userid=:userid order by created");
			$stmt->bindParam(':userid', $userid);

			$count = 0;
			$budgetId = "";
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					if ($count == 0){
						$budgetId = $row["id"];
						$count++;
					} else{ break;
					}
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('018');
		}

		return $budgetId;
	}

	public function getUnreconciledTransactions($startDate, $endDate, $userid){
		$savingsAccId = $this->getSavingsAccountId($userid);
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select t.id, t.description, t.date, t.amount, t.bankRef, t.budgetitemid, t.reconciled, t.categoryId from transaction t, account a where t.accountId=a.id and t.reconciled is false and t.accountId<>:savingsAccId and a.userid=:userid and date(t.date) > :startDate and date(t.date) < :endDate");
			$stmt->bindParam(':startDate', $startDate);
			$stmt->bindParam(':endDate', $endDate);
			$stmt->bindParam(':userid', $userid);
			$stmt->bindParam(':savingsAccId', $savingsAccId);

			$itemslist = array();

			$util = new Util;

			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$transId = $row["id"];
					$description = $row["description"];
					$date = $row["date"];
					$transType = $row["amount"] < 0 ? "expense" : "income";
					$transAmount = $row["amount"] < 0 ? $row["amount"]*-1 : $row["amount"];
					$bankRef = $row["bankRef"];
					$budgetitemid = $row["budgetitemid"];
					$reconciled = 0;
					$categoryId = $row["categoryId"];

					$transaction = new Transaction($transId, $description, $date, $transAmount, $util->ShortenString($bankRef, 30), $transType, $reconciled, $budgetitemid, $categoryId);
					array_push($itemslist, $transaction);
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('005');
		}

		return $itemslist;
	}

	public function insertBudgetItemIntoBudget($budgetItemId, $budgetId, $budgetAmount, $actualAmount){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("insert into budget_budgetitem (budgetid, budgetitemid, budgetAmount, actualAmount) values (:budgetid, :budgetItemId, :budgetAmount, :actualAmount)");
			$stmt->bindParam(':budgetItemId', $budgetItemId);
			$stmt->bindParam(':budgetid', $budgetId);
			$stmt->bindParam(':budgetAmount', $budgetAmount);
			$stmt->bindParam(':actualAmount', $actualAmount);

			$stmt->execute();

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('017');
		}
	}



	public function getMasterReconcileData($parameters){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from masterautoreconcile order by length(akey) desc");

			$autoreconciles = array();
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$autoreconcile = new AutoReconcile($row["categoryId"], $row["akey"], $row["avalue"]);
					$autoreconcile->group = $row["budgetItemDesc"];
					array_push($autoreconciles, $autoreconcile);
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('018');
		}

		return $autoreconciles;
	}

	public function createBudget($parameters){
		$budgetDate = $parameters["budgetDate"];
		$userid = $parameters["userid"];

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("insert into budget (userid, month, created, carryover) values (:userid, :month, NOW(), 0)");
			$stmt->bindParam(':userid', $userid);
			$stmt->bindParam(':month', $budgetDate);

			$stmt->execute();

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('017');
		}

		$responder = new Responder;
		return $responder->constructResponse(null);
	}

	public function getBudgetIdForLastBudgetForUser($userid){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select max(id) as id from budget where userid=:userid");
			$stmt->bindParam(':userid', $userid);

			$id = "";
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$id = $row["id"];
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('012');
		}
		return $id;
	}

	public function deleteAccount($parameters){
		$accountId = $parameters["accountId"];

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("delete from transaction where accountId = :accountId");
			$stmt->bindParam(':accountId', $accountId);

			if ($stmt->execute()){
				$stmt = $con->prepare("delete from account where id = :accId");
				$stmt->bindParam(':accId', $accountId);

				$stmt->execute();
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('017');
		}

		$responder = new Responder;
		return $responder->constructResponse(null);
	}

	public function transferFunds($parameters){
		$amount = $parameters["amount"];
		$fromSubAccId = $parameters["fromSubAccId"];
		$toSubAccId = $parameters["toSubAccId"];
		$fromSubAccDesc = "Transfer To '" .$parameters["toSubAccDesc"]. "'";
		$toSubAccDesc = "Transfer from '" .$parameters["fromSubAccDesc"]. "'";

		$savingsAcc = $this->getSavingsAccountId($parameters["userid"]);

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare($query = "insert into transaction (accountId, date, amount, description, reconciled, categoryId, budgetitemid, parentTransId) values (:savingsAccId, NOW(), :transAmount, :transferFrom, 1, 4, :budgetItemId, 0)");
			$amount = $amount * -1;
			$stmt->bindParam(':transAmount', $amount);
			$stmt->bindParam(':budgetItemId', $fromSubAccId);
			$stmt->bindParam(':savingsAccId', $savingsAcc);
			$stmt->bindParam(':transferFrom', $fromSubAccDesc);

			if ($stmt->execute()){

				$stmt = $con->prepare($query = "insert into transaction (accountId, date, amount, description, reconciled, categoryId, budgetitemid, parentTransId) values (:savingsAccId, NOW(), :transAmount, :transferTo, 1, 4, :budgetItemId, 0)");
				$amount = $amount * -1;
				$stmt->bindParam(':transAmount', $amount);
				$stmt->bindParam(':budgetItemId', $toSubAccId);
				$stmt->bindParam(':savingsAccId', $savingsAcc);
				$stmt->bindParam(':transferTo', $toSubAccDesc);
				$stmt->execute();
			}


		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('017');
		}

		$responder = new Responder;
		return $responder->constructResponse(null);
	}

	public function getSavingsSubAccounts($parameters){
		$savingsAcc = $this->getSavingsAccountId($parameters["userid"]);
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select s.id, s.budgetitemid, b.description, s.openingBalance from subaccount s, budgetitem b where s.budgetitemid=b.id and s.parentAccId=:savingAccId");
			$stmt->bindParam(':savingAccId', $savingsAcc);

			$itemslist = array();
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$id = $row["id"];
					$budgetitemid = $row["budgetitemid"];
					$description = $row["description"];
					$openingBalance = $row["openingBalance"];

					$closingBalance = $openingBalance + self::getBalanceForSubAccount($budgetitemid, $savingsAcc);

					$subAccount = new SubAccount($id, $budgetitemid, $description, $openingBalance, $closingBalance);
					array_push($itemslist, $subAccount);
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('012');
		}

		$responder = new Responder;
		return $responder->constructResponse($itemslist);
	}

	public function deleteAutoReconcile($parameters){
		$key = $parameters["key"];
		$value = $parameters["value"];
		$userid = $parameters["userid"];

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("delete from autoreconcile where akey= :key and avalue= :value and userid=:userid");
			$stmt->bindParam(':key', $key);
			$stmt->bindParam(':value', $value);
			$stmt->bindParam(':userid', $userid);

			$stmt->execute();

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('017');
		}

		$responder = new Responder;
		return $responder->constructResponse(null);
	}

	public function getAutoReconcileData($parameters){
		$userid = $parameters["userid"];
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from autoreconcile where userid=:userid order by length(akey) desc");
			$stmt->bindParam(':userid', $userid);

			$autoreconciles = array();
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					array_push($autoreconciles, new AutoReconcile($row["budgetitemid"], $row["akey"], $row["avalue"]));
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('018');
		}

		$responder = new Responder;
		return $responder->constructResponse($autoreconciles);
	}

	public function insertAutoReconcileIntoMasterList($categoryId, $key, $value, $description){
		$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
		$stmt = $con->prepare("insert into masterautoreconcile (categoryId, akey, avalue, budgetItemDesc) values (:categoryId, :key, :value, UCASE(:description))");
		$stmt->bindParam(':categoryId', $categoryId);
		$stmt->bindParam(':key', $key);
		$stmt->bindParam(':value', $value);
		$stmt->bindParam(':description', $description);

		$stmt->execute();
	}

	public function savePreferences($parameters){
		$startOfMonthDate = $parameters["startOfMonthDate"];
		$userid = $parameters["userid"];
		$accountId = $parameters["accountId"];
		$budgetitemid = $parameters["budgetitemId"];
		
		try {
			
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("update config set cvalue = :startOfMonthDate where ckey = 'firstdayofmonth' and userid=:userid");
			$stmt->bindParam(':startOfMonthDate', $startOfMonthDate);
			$stmt->bindParam(':userid', $userid);

			$stmt->execute();

			//Update user details
			$securityService = new SecurityService;
			$savingsAccId = $securityService->updateUserDetails($parameters); 

		

		
		//if (($accountId != "-1") && ($budgetitemid =! "-1")){
			self::saveBudgetItemAccountAssociation($budgetitemid, $accountId);
		//}
		
		} catch (Exception $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('019');
		}
		
		$responder = new Responder;
		return $responder->constructResponse(null);
	}

	public function getStartOfMonthDate($userid){
		$startOfMonth = 0;
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from config where ckey='firstdayofmonth' and userid=:userid");
			$stmt->bindParam(':userid', $userid);

			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$startOfMonth = $row["cvalue"];
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('018');
		}
		return $startOfMonth;
	}

	public function setStartOfMonthDate($monthStartDate, $userid){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("insert into config values ('firstdayofmonth', :monthStartDate, :userid)");
			$stmt->bindParam(':userid', $userid);
			$stmt->bindParam(':monthStartDate', $monthStartDate);

			$stmt->execute();

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('018');
		}
	}

	public function deleteBudget($parameters){
		$budgetId = $parameters["budgetId"];

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("delete from budgetitem where budgetid = :budgetId");
			$stmt->bindParam(':budgetId', $budgetId);

			$stmt->execute();

			$stmt = $con->prepare("delete from budget where id = :budgetId");
			$stmt->bindParam(':budgetId', $budgetId);

			$stmt->execute();


		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('017');
		}

		$responder = new Responder;
		return $responder->constructResponse(null);
	}

	public function getReport($parameters){
		$reportType= $parameters["reportType"];
		$toDate= $parameters["toDate"];
		$fromDate= $parameters["fromDate"];
		$savingsAccId = $this->getSavingsAccountId($parameters["userid"]);

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);

			$itemslist = array();
			if($reportType=="incomeAndExpense"){
				$stmt = $con->prepare("select t.id, t.date, t.description, t.amount, t.categoryId from transaction t, account a where a.userid=:userid and t.accountId=a.id and a.type=0 and date(date) >= :fromDate and date(date) < :toDate");
				$stmt->bindParam(':fromDate', $fromDate);
				$stmt->bindParam(':toDate', $toDate);
				$stmt->bindParam(':userid', $parameters["userid"]);

				if ($stmt->execute()){
					while ($row = $stmt->fetch()){
						$transId = $row["id"];
						$description = $row["description"];
						$date = $row["date"];
						$transAmount = $row["amount"];
						$bankRef = "";
						$budgetitemid = "";
						$reconciled = "";
						$categoryId = $row["categoryId"];
						$transType = "";

						$transaction = new Transaction($transId, $description, $date, $transAmount, $bankRef, $transType, $reconciled, $budgetitemid, $categoryId);
						array_push($itemslist, $transaction);
					}
				}
			} else if($reportType=="categories"){
				$stmt = $con->prepare("select t2.description description, t2.id id , sum(t1.amount) amount from transaction t1, budgetitem t2, account a where a.userid=:userid and t1.budgetitemid = t2.id and a.id=t1.accountId and accountId<>:savingsAccId and date(date) >=:fromDate and date(date) <:toDate group by t2.description, t2.id");
				$stmt->bindParam(':fromDate', $fromDate);
				$stmt->bindParam(':toDate', $toDate);
				$stmt->bindParam(':savingsAccId', $savingsAccId);
				$stmt->bindParam(':userid', $parameters["userid"]);

				if ($stmt->execute()){
					while ($row = $stmt->fetch()){
						$transId = $row["id"];
						$description = $row["description"];
						$date = "";
						$transAmount = $row["amount"];
						$bankRef = "";
						$budgetitemid = "";
						$reconciled = "";
						$categoryId = "";
						$transType = "";

						$transaction = new Transaction($transId, $description, $date, $transAmount, $bankRef, $transType, $reconciled, $budgetitemid, $categoryId);
						array_push($itemslist, $transaction);
					}
				}

			}
		}catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('012');
		}

		$responder = new Responder;
		return $responder->constructResponse($itemslist);
	}

	public function getBudgetItemsForBudget($parameters){
		$categoryId = $parameters["categoryId"];
		$budgetId = $parameters["budgetId"];
		$userid = $parameters["userid"];

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from budget_budgetitem where budgetid= :budgetId");
			$stmt->bindParam(':budgetId', $budgetId);

			$existingBudgetItems = array();
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					array_push($existingBudgetItems, $row["budgetitemid"]);
				}
			}

			$stmt = $con->prepare("select * from budgetitem where categoryId= :categoryId and userid=:userid");
			$stmt->bindParam(':categoryId', $categoryId);
			$stmt->bindParam(':userid', $userid);

			$itemslist = array();

			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					if (in_array($row["id"], $existingBudgetItems)){
						continue;
					}
					$budgetitem = new BudgetItem($row["id"], $row["categoryId"], $row["description"], $row["expense"]);
					array_push($itemslist, $budgetitem);
				}
			}


		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('016');
		}

		$responder = new Responder;
		return $responder->constructResponse($itemslist);
	}

	public function createAccount($parameters){
		$accountName = $parameters["accountName"];
		$accountNum = $parameters["accountNum"];
		$openingBal = $parameters["openingBal"];
		$accountType = $parameters["accountType"];
		$statementBalance = $parameters["statementBalance"];
		$availableBal = $parameters["availableBal"];
		$userid = $parameters["userid"];

		try {
			if ($accountType == "expenseSavings"){
				$accountType = 1;
			} else if ($accountType == "normal"){
				$accountType = 0;
			}


			error_log("HERE1");
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("insert into account (name, userid, accNum, creationdate, active, balance, available, stmtBalance, openingBalance, type) values (:accountName, :userid, :accountNum, Now(), 1, :availableBal, :availableBal, :statementBalance, :openingBal, :accountType)");
			$stmt->bindParam(':accountName', $accountName);
			$stmt->bindParam(':accountNum', $accountNum);			
			$stmt->bindParam(':availableBal', $availableBal);
			$stmt->bindParam(':statementBalance', $statementBalance);
			$stmt->bindParam(':openingBal', $openingBal);
			$stmt->bindParam(':accountType', $accountType);
			$stmt->bindParam(':userid', $userid);
			error_log("HERE2");
			$stmt->execute();
			error_log("HERE3");

		} catch (Exception $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('016');
		}

		$responder = new Responder;
		return $responder->constructResponse(null);
	}
	
	public function updateAccountDetails($parameters){
		$accountId = $parameters["accountId"];
		$accountName = $parameters["accountName"];
		$accountNum = $parameters["accountNum"];
		$accountType = $parameters["accountType"];
		$statementBalance = $parameters["statementBalance"];
		$availableBal = $parameters["availableBal"];
		$openingBal = $parameters["openingBal"];
		
		$query = "update account set name= :accountName, accNum= :accountNum, active=:active, type= :accountType where id= :id";
		if($statementBalance != null){
			$query = "update account set name= :accountName, accNum= :accountNum, active=:active, type= :accountType, openingBalance= :openingBal, stmtBalance= :statementBalance, available =:availableBal where id= :id";
		}
		
		$active = $parameters["active"];
		error_log("getting here");
		try {
			if ($accountType == "expenseSavings"){
				$accountType = 1;
			} else if ($accountType == "normal"){
				$accountType = 0;
			}
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare($query);
			$stmt->bindParam(':accountName', $accountName);
			$stmt->bindParam(':accountNum', $accountNum);
			$stmt->bindParam(':active', $active);
			$stmt->bindParam(':accountType', $accountType);
			if($statementBalance != null){
				$stmt->bindParam(':statementBalance', $statementBalance);
				$stmt->bindParam(':availableBal', $availableBal);
				$stmt->bindParam(':openingBal', $openingBal);
			}
			$stmt->bindParam(':id', $accountId);
			$stmt->execute();
			
		} catch (Exception $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('016');
		}
		
		$responder = new Responder;
		return $responder->constructResponse(null);
	} 

	public function deleteTransaction($parameters){
		$transactionId = $parameters["transactionId"];
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("delete from transaction where id= :transactionId");
			$stmt->bindParam(':transactionId', $transactionId);

			$stmt->execute();

		} catch (Exception $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('015');
		}

		$responder = new Responder;
		return $responder->constructResponse(null);
	}

	public function doSavingsReconcile($parameters){
		$accId = $parameters["accId"];
		$mainTransId = $parameters["mainTransId"];
		$description = $parameters["reconTransDescription"];
		$transId =  split(",",$parameters["transId"]);
		$newTransDate =  split(",",$parameters["newTransDate"]);
		$newTransDescription =  split(",",$parameters["newTransDescription"]);
		$newTransAmount =  split(",",$parameters["newTransAmount"]);
		$newTransBudgetItemId =  split(",",$parameters["newTransBudgetItemId"]);

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);

			//Update main transaction
			$stmt = $con->prepare("update transaction set reconciled = true where id= :mainTransId");
			$stmt->bindParam(':mainTransId', $mainTransId);
			if ($stmt->execute()){

				//We update the existing transactions
				$count=0;
				foreach ($transId as  $row){
					if($count != 0){
						$stmt = $con->prepare("update transaction set reconciled = true, parentTransId=:mainTransId where id=:transId");
						$stmt->bindParam(':mainTransId', $mainTransId);
						$stmt->bindParam(':transId', $transId[$count]);

						$stmt->execute();
					}
					$count++;
				}

				$count=0;
				foreach ($newTransDate as  $row){

					if($count != 0){

						//This caters for filler transactions
						$reconciled = 1;
						if (($newTransBudgetItemId[$count]) == "filler"){
							$newTransBudgetItemId[$count] = 313;
							$reconciled = 0;
							$mainTransId = 0;
						}

							
						$stmt = $con->prepare("insert into transaction (accountId, date, amount, description, reconciled, categoryId, budgetitemid, parentTransId) values (:accId, :date, :amount, :description, :reconciled, 4, :budgetItemId, :transId)");
						$stmt->bindParam(':accId', $accId);
						$stmt->bindParam(':date', $newTransDate[$count]);
						$stmt->bindParam(':amount', $newTransAmount[$count]);
						$stmt->bindParam(':description', $newTransDescription[$count]);
						$stmt->bindParam(':budgetItemId', $newTransBudgetItemId[$count]);
						$stmt->bindParam(':transId', $mainTransId);
						$stmt->bindParam(':reconciled', $reconciled);

						$stmt->execute();
					}
					$count++;
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('007');
		}

		$responder = new Responder;
		return $responder->constructResponse(null);
	}

	public function registerSavings($parameters){
		$budgetId = $parameters["budgetId"];
		$expenseSavingsActuals = split(",", $parameters["expenseSavingsActual"]);
		$budgetItemIds = split(",", $parameters["expenseSavingsRowId"]);
		$expenseSavingsDescriptions = split(",", $parameters["expenseSavingsDescription"]);
		$savingsAccountId = $this->getSavingsAccountId($parameters["userid"]);

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);

			$count = 0;
			foreach ($budgetItemIds as $row) {
				if (($count != 0) && ($expenseSavingsActuals[$count] != "0")) {
					$stmt = $con->prepare("insert into  transaction (accountId, date, amount, description, reconciled, categoryId, budgetitemid, parentTransId) values (:savingsAccountId, NOW(), :actualAmount, :description, 0, 4, :budgetItemId, 0)");
					$stmt->bindParam(':savingsAccountId', $savingsAccountId);
					$stmt->bindParam(':actualAmount', $expenseSavingsActuals[$count]);
					$stmt->bindParam(':description', $expenseSavingsDescriptions[$count]);
					$stmt->bindParam(':budgetItemId',  $budgetItemIds[$count]);

					$stmt->execute();
				}
				$count = $count +1;
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('007');
		}

		$responder = new Responder;
		return $responder->constructResponse(null);
	}

	public function getSavingsTransactions($parameters){
		$categoryId = $parameters["categoryId"];
		$budgetItemId = $parameters["budgetItemId"];
		$accountId = $parameters["accountId"];

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from transaction where budgetitemid=:budgetItemId and categoryId=:categoryId and accountId=:accountId order by date desc limit 15");
			$stmt->bindParam(':categoryId', $categoryId);
			$stmt->bindParam(':budgetItemId', $budgetItemId);
			$stmt->bindParam(':accountId', $accountId);

			$itemslist = array();
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$transId = $row["id"];
					$description = $row["description"];
					$date = $row["date"];
					$transType = "";
					$transAmount = $row["amount"];
					$transType = "";
					$bankRef = $row["bankRef"];
					$budgetitemid = $row["budgetitemid"];
					$reconciled = $row["reconciled"];
					$categoryId = $row["categoryId"];

					$transaction = new Transaction($transId, $description, $date, $transAmount, $bankRef, $transType, $reconciled, $budgetitemid, $categoryId);
					array_push($itemslist, $transaction);
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('012');
		}

		$responder = new Responder;
		return $responder->constructResponse($itemslist);
	}

	private function getAccountForBudgetItemAssociation($budgetitemid){
		$accountid = -1;
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select accountid from budgetitem_account where budgetitemid = :budgetitemid");
			$stmt->bindParam(':budgetitemid', $budgetitemid);
		
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$accountid = $row["accountid"];
				}
			}
		
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('018');
		}
		return $accountid;
	}
	
	public function saveTransaction($parameters){
		$responder = new Responder;
		$transId = $parameters["transId"];
		$transDate = $parameters["transDate"];
		$transDescription = $parameters["transDescription"];
		$autoReconcile = $parameters["autoReconcile"];
		$transDescriptor = $parameters["transDescriptor"];
		$categoryId = $parameters["categoryId"];
		$reconciled = $parameters["reconciled"];
		$transAmount = $parameters["transAmount"];
		$budgetItemId = $parameters["budgetItemId"];
		$accId = $parameters["accId"];
		$userid = $parameters["userid"];
		$isEnterTransfer = "false";
		
		
		$associatedAccountId = self::getAccountForBudgetItemAssociation($budgetItemId);
		
		if (($associatedAccountId != -1) || ($categoryId == 6) || ($categoryId == 7) || ($categoryId == 4)){
			$transferAccountId = $budgetItemId;
			
			if ($associatedAccountId != -1){
				$transferAccountId = $associatedAccountId;
			} else if ($categoryId == 4){
				$savingsAccId = $this->getSavingsAccountId($parameters["userid"]);
				$transferAccountId = $savingsAccId;
			}

			$confirmTransInsert = $parameters["confirmTransInsert"];

			$potentialDuplicateTrans = $this->isThereAPosibilityOfDuplicateTransaction($transferAccountId, $transAmount, $transDescriptor, $transDate);

			error_log("POSIBILITY: ". count($potentialDuplicateTrans));

			if ((count($potentialDuplicateTrans) > 0) && ($confirmTransInsert == "undefined")) {
				return $responder->constructErrorArrayResponse('026', $potentialDuplicateTrans);
			}


			if (($confirmTransInsert == "true") || (count($potentialDuplicateTrans) == 0)){
				$isEnterTransfer = "true";
			}

			error_log("isEnterTransfer: ". $isEnterTransfer);
		}

		try {

			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);

			if ($transId == ""){
				/** INSERT TRANSACTION **/
				$query = "insert into transaction (accountId, date, amount, description, reconciled, categoryId, budgetitemid) values (:accId, :transDate, :transAmount, :transDescription, true, :categoryId, :budgetItemId)";
				$stmt = $con->prepare($query);
				$stmt->bindParam(':accId', $accId);
			} else {
				/** UPDATE TRANSANCTION **/
				$query = "update transaction set date=:transDate, amount= :transAmount, description= :transDescription, reconciled=true, categoryId= :categoryId, budgetitemid= :budgetItemId where id= :transId";
				$stmt = $con->prepare($query);
				$stmt->bindParam(':transId', $transId);
			}


			$stmt->bindParam(':transDate', $transDate);
			$stmt->bindParam(':transDescription', $transDescription);
			$stmt->bindParam(':transAmount', $transAmount);
			$stmt->bindParam(':categoryId', $categoryId);
			$stmt->bindParam(':budgetItemId', $budgetItemId);

			if ($stmt->execute()){

				if ($isEnterTransfer == "true") {
					error_log("entering tranfer....");
					$transAmount = ($categoryId == 4 ? $transAmount : ($transAmount * -1));

					if (($categoryId == 6) || ($categoryId == 4)){
						$reconciled = 0;
					}
					
					if ($associatedAccountId != -1){
						$budgetItemId = 0;
					}

					$theDescription = $transDescriptor == "" ? $transDescription : $transDescriptor;

					//If it is a transfer transaction
					$accountId = $budgetItemId;
					$query = "insert into transaction (accountId, date, amount, description, bankRef, reconciled, categoryId, budgetitemid, parentTransId) values (:accountId, :transDate, :transAmount, :transDescription, :bankRef, :reconciled, :categoryId, :budgetItemId, 0)";
					$stmt = $con->prepare($query);

					$stmt->bindParam(':accountId', $transferAccountId);
					$stmt->bindParam(':transDate', $transDate);
					$stmt->bindParam(':transAmount', $transAmount);
					$stmt->bindParam(':transDescription', $transDescription);
					$stmt->bindParam(':categoryId', $categoryId);
					$stmt->bindParam(':budgetItemId', $budgetItemId);
					$stmt->bindParam(':reconciled', $reconciled);
					$stmt->bindParam(':bankRef', $theDescription);


					try {
						$stmt->execute();
					} catch (PDOException $e){
						throw new Exception('025');
					}
				}


				/** DEFINE AUTO_RECONCILE **/
				if ($autoReconcile == "true") {
					$query = "insert into autoreconcile values (:transDescriptor, :budgetItemId, :transDescription, :userid)";
					$stmt = $con->prepare($query);

					$stmt->bindParam(':transDescriptor', $transDescriptor);
					$stmt->bindParam(':budgetItemId', $budgetItemId);
					$stmt->bindParam(':transDescription', $transDescription);
					$stmt->bindParam(':userid', $userid);

					$stmt->execute();

					$budgetItem = $this->getBudgetItemById($budgetItemId);

					try {
						$this->insertAutoReconcileIntoMasterList($categoryId, $transDescriptor, $transDescription, $budgetItem->description);
					} catch (PDOException $e){
						//Do nothing, probably key already exists.
					}

					try {
							
						$query = "update transaction set reconciled=1, categoryId=:categoryId , description=:transDescription, budgetitemid=:budgetItemId where reconciled=0 and bankRef like '%" . $transDescriptor . "%'";
						$stmt = $con->prepare($query);
						$stmt->bindParam(':budgetItemId', $budgetItemId);
						$stmt->bindParam(':categoryId', $categoryId);
						$stmt->bindParam(':transDescription', $transDescription);
						$stmt->execute();

					} catch (PDOException $e){

					}

				}
			}


		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('010');
		}


		return $responder->constructResponse(null);
	}

	public function getBudgetItemById($budgetItemId){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from budgetitem where id=:budgetItemId");
			$stmt->bindParam(':budgetItemId', $budgetItemId);

			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$budgetitem = new BudgetItem($row["id"], $row["categoryId"], $row["description"], $row["expense"]);
				}
			}


		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
		}

		return $budgetitem;
	}

	public function getAccountsAsBudgetItems($parameters){
		$userid = $parameters["userid"];
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from account where userid=:userid");
			$stmt->bindParam(':userid', $userid);

			$itemslist = array();

			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$budgetitem = new BudgetItem($row["id"], "6", $row["name"], "0");
					array_push($itemslist, $budgetitem);
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('009');
		}

		$responder = new Responder;
		return $responder->constructResponse($itemslist);
	}

	public function getAllBudgetItems($parameters){
		$userid = $parameters["userid"];
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$sql = "select * from budgetitem where userid=:userid";
			$stmt = $con->prepare($sql);
			$stmt->bindParam(':userid', $userid);

			$itemslist = array();

			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$budgetitem = new BudgetItem($row["id"], $row["categoryId"], $row["description"], $row["expense"]);
					$service = new CommonServices;
					$budgetitem->existInCurrentBudget = $this->isBudgetItemExistInBudget($row["id"], $service->getCurrentBudgetId($userid));
					array_push($itemslist, $budgetitem);
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('008');
		}

		$responder = new Responder;
		return $responder->constructResponse($itemslist);
	}


	private function isBudgetItemExistInBudget($budgetItemId, $budgetId){
		$result = "false";
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from budget_budgetitem where budgetid=:budgetid and budgetitemid=:budgetitemid");
			$stmt->bindParam(':budgetid', $budgetId);
			$stmt->bindParam(':budgetitemid', $budgetItemId);

			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$id = $row["id"];
					if ($id != ""){
						$result = "true";
					}
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('007');
		}
		return $result;
	}

	public function getAllTransactionsForAccount($accountId){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from transaction where accountId =:accountId");
			$stmt->bindParam(':accountId', $accountId);

			$itemslist = array();
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$transId = $row["id"];
					$description = $row["description"];
					$date = $row["date"];
					$transType = "";
					$transAmount = $row["amount"];
					$bankRef = $row["bankRef"];
					$budgetitemid = $row["budgetitemid"];
					$reconciled = $row["reconciled"];
					$categoryId = $row["categoryId"];

					$transaction = new Transaction($transId, $description, $date, $transAmount, $bankRef, $transType, $reconciled, $budgetitemid, $categoryId);
					array_push($itemslist, $transaction);
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('005');
		}

		return $itemslist;
	}

	public function getTransaction($parameters){
		$transId = $parameters["transId"];
		error_log("getTransaction: ".$transId);
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from transaction where id =:transId");
			$stmt->bindParam(':transId', $transId);

			error_log("BEfore execute");
			if ($stmt->execute()){
				error_log("AFter execute");
				while ($row = $stmt->fetch()){
					error_log("In Row");
					$description = $row["description"];
					$date = $row["date"];
					$transType = "";
					if ($row["amount"] < 0) {
						$transAmount = $row["amount"] * -1;
						$transType = "expense";
					} else {
						$transAmount = $row["amount"];
						$transType = "income";
					}
					$bankRef = $row["bankRef"];
					$budgetitemid = $row["budgetitemid"];
					$reconciled = $row["reconciled"];
					$categoryId = $row["categoryId"];

					error_log("DESCRIPTION: ".$description);

					$transaction = new Transaction($transId, $description, $date, $transAmount, $bankRef, $transType, $reconciled, $budgetitemid, $categoryId);
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('005');
		}

		$responder = new Responder;
		return $responder->constructResponse($transaction);
	}

	public function deleteBudgetItem($parameters){
		$budgetItemId = $parameters["budgetItemId"];
		$budgetId = $parameters["budgetId"];
		if ($budgetId == "current"){
			$service = new CommonServices;
			$budgetId = $service->getCurrentBudgetId($parameters["userid"]);
		}

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("delete from budget_budgetitem where budgetitemid = :budgetItemId and budgetid= :budgetId");
			$stmt->bindParam(':budgetId', $budgetId);
			$stmt->bindParam(':budgetItemId', $budgetItemId);

			$stmt->execute();

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('006');
		}

		$responder = new Responder;
		return $responder->constructResponse(null);
	}

	public function saveAccountDetails($parameters){
		$accId = $parameters["accId"];
		$openingBalance = $parameters["openingBalance"];
		$accNum = $parameters["accNum"];
		$accActive = $parameters["accActive"];
		$accName = $parameters["accName"];

		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$stmt = $con->prepare("update account set name= :accName, accNum= :accNum, active= :accActive, openingBalance= :openingBalance where id= :accId");
			$stmt->bindParam(':accId', $accId);
			$stmt->bindParam(':openingBalance', $openingBalance);
			$stmt->bindParam(':accNum', $accNum);
			$stmt->bindParam(':accActive', $accActive);
			$stmt->bindParam(':accName', $accName);

			$stmt->execute();

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('007');
		}

		$responder = new Responder;
		return $responder->constructResponse(null);
	}

	public function getSavingsAccountId($userid){
		$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
		$stmt = $con->prepare("select id from account where type=1 and userid=:userid");
		$stmt->bindParam(':userid', $userid);

		$id = -1;
		if ($stmt->execute()){
			while ($row = $stmt->fetch()){
				$id = $row["id"];
			}
		}
		return $id;
	}

	public function getFirstTransactionalAccountId($userid){
		$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
		$stmt = $con->prepare("select id from account where type<>1 and userid=:userid");
		$stmt->bindParam(':userid', $userid);

		$id = -1;
		if ($stmt->execute()){
			while ($row = $stmt->fetch()){
				$id = $row["id"];
			}
		}
		return $id;
	}

	public function updateAccountBalance($accountId, $statementBal){
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("update account set stmtBalance=:statementBal where id=:accountId");
			$stmt->bindParam(':statementBal', $statementBal);
			$stmt->bindParam(':accountId', $accountId);
			$stmt->execute();

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('007');
		}
	}

	public function isThisMyBudget($budgetId){
		$userId = $_SESSION["userid"];
		$result = false;
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select id from budget where id=:budgetId and userid=:userId");
			$stmt->bindParam(':budgetId', $budgetId);
			$stmt->bindParam(':userId', $userId);
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$id = $row["id"];
					if (isset($id)){
						$result = true;
					}
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('007');
		}
		return $result;
	}


	/**
	 * Used to check if there is a transaction in the account with the same amount within a proximity of a couple of days. This might indicate taht we are duplicating the transactions.
	 */
	public function isThereAPosibilityOfDuplicateTransaction($accId, $amount, $description, $transDate){
		$amount = ($amount * -1);
		try {
			$con = new PDO(GlobalConfig::db_pdo_connect_string, GlobalConfig::db_username, GlobalConfig::db_password);
			$stmt = $con->prepare("select * from transaction where accountId=:accId and (amount=:amount or amount=(:amount * -1)) and date >= DATE_ADD(:transDate, INTERVAL -7 DAY) and date < DATE_ADD(:transDate, INTERVAL 7 DAY)");
			$stmt->bindParam(':accId', $accId);
			$stmt->bindParam(':amount', $amount);
			$stmt->bindParam(':transDate', $transDate);

			$itemslist = array();
			if ($stmt->execute()){
				while ($row = $stmt->fetch()){
					$transId = $row["id"];
					$description = $row["description"];
					$date = $row["date"];
					$transType = "";
					$transAmount = $row["amount"];
					$bankRef = $row["bankRef"];
					$budgetitemid = $row["budgetitemid"];
					$reconciled = $row["reconciled"];
					$categoryId = $row["categoryId"];

					$transaction = new Transaction($transId, $description, $date, $transAmount, $bankRef, $transType, $reconciled, $budgetitemid, $categoryId);
					array_push($itemslist, $transaction);
				}
			}

		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
			throw new Exception('007');
		}
		return $itemslist;
	}
	
	public function getAccountTypes(){
		$accountTypes = array();
		$con = mysql_connect(GlobalConfig::db_host_name,GlobalConfig::db_username,GlobalConfig::db_password);
		mysql_select_db(GlobalConfig::db_name, $con);
		$query = "select * from account_type";
		error_log($query);
	
		$result = mysql_query($query);
		while ($row = mysql_fetch_array($result)){
			$accountTypes["".$row['id'].""] = $row["name"];			
		}				
		return $accountTypes;
	}
}
?>